﻿using System;
using System.Text;
using System.Windows;
using System.Windows.Forms;
using Microsoft.Win32;
using System.Xml;
using System.Collections.ObjectModel;
using System.IO;
using System.Windows.Controls.Primitives;
using System.Collections.Generic;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Data;
using System.Collections;
using System.ComponentModel;

namespace ExportLSDbasScript
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        Collection<string> colScript = new Collection<string>();
        StringBuilder strScript = new StringBuilder();

        public MainWindow()
        {
            InitializeComponent();
        }

        private void btnBrowse_Click(object sender, RoutedEventArgs e)
        {
            txtProg.Text = "";

            FolderBrowserDialog fbd = new FolderBrowserDialog();

            fbd.ShowNewFolderButton = false;
            fbd.RootFolder = Environment.SpecialFolder.MyComputer;

            if (fbd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtProg.Text = "Please wait.....";

                var files = Directory.GetFiles(fbd.SelectedPath, "*.ssdl", SearchOption.AllDirectories);

                if (files.Length > 0)
                {
                    List<DBDetails> DBDetailsCollection = new List<DBDetails>();

                    foreach (var item in files)
                    {
                        FileInfo fi = new FileInfo(item);
                        DBDetailsCollection.Add(
                            new DBDetails() { 
                                                DatabaseName = fi.Name.Replace(".ssdl", string.Empty), 
                                                DatabaseSize = fi.Length.ToString() + " Bytes in size", 
                                                DatabaseFilename = fi.FullName, IsDropChecked = true 
                                             });
                    }

                    lstData.ItemsSource = DBDetailsCollection;

                    popupListDB.IsOpen = true;

                    txtProg.Text = "";

                }
                else
                {
                    txtProg.Text =  "No data sources found in the selected LightSwitch application !";
                }

            }

        }

        private string GenerateConstraintCheckScript(XmlNodeList xmlNLAssociation)
        {
            StringBuilder script = new StringBuilder(Environment.NewLine + "--Creating CHECK constraints for the tables starts here--");

            foreach (XmlNode item in xmlNLAssociation)
            {
                string[] names = item.Attributes["Name"].Value.StartsWith("FK_", StringComparison.CurrentCulture) ? item.Attributes["Name"].Value.Split('_') : ("FK_" + item.Attributes["Name"].Value).Split('_');

                if (names.Length >1)
                {
                    script.Append(string.Format(
                                                  "{0}IF  EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{2}]') AND parent_object_id = OBJECT_ID(N'[dbo].[{1}]'))"
                                                 +"{0}BEGIN"
                                                 +"{0}      ALTER TABLE [dbo].[{1}]"
                                                 +"{0}      WITH CHECK CHECK CONSTRAINT {2} "
                                                 +"{0}      PRINT 'CHECK executed successfully on ''{2}''..'"
                                                 +"{0}END"
                                                 +"{0}ELSE"
                                                 +"{0}BEGIN"
                                                 +"{0}     PRINT 'The Foreign Key ''{2}'' does not exists ! Cant execute CHECK.'"
                                                 +"{0}END",
                                                 Environment.NewLine,
                                                 names[1],
                                                 item.Attributes["Name"].Value.StartsWith("FK_", StringComparison.CurrentCulture) ? item.Attributes["Name"].Value : ("FK_" + item.Attributes["Name"].Value))); 
                }
            }

            return script.Append(Environment.NewLine + "--Creating CHECK constraints for the tables ends here--" ).ToString();
        }

        private string GenerateConstraintScript(XmlNodeList xmlNLAssociation,bool drop)
        {
            StringBuilder script = new StringBuilder(string.Empty);
            
            foreach (XmlNode item in xmlNLAssociation)
            {
                if (item.HasChildNodes)
                {
                    string scrt = GenerateConstraintScript(item.ChildNodes,drop);

                    if (!scrt.Equals(string.Empty))
                    {
                        if (item.Name.Equals("Dependent"))
                        {
                            strScript.Insert(0,scrt);
                            colScript.Add(strScript.ToString());
                            strScript = new StringBuilder(string.Empty);
                        }
                        else
                        {
                            strScript.Insert(0,scrt);
                        }
                    }
                }
                else
                {
                    if (!item.Name.Equals("End"))
                    {
                        switch (item.ParentNode.Name)
                        {
                            case "Principal":
                                string princName = (item.ParentNode.Attributes["Role"].Value == item.ParentNode.ParentNode.ParentNode.ChildNodes[0].Attributes["Role"].Value) ? item.ParentNode.ParentNode.ParentNode.ChildNodes[0].Attributes["Type"].Value.Split('.')[1] : item.ParentNode.Attributes["Role"].Value;
                                if (!drop)
                                {
                                    if (item.ParentNode.ParentNode.ParentNode.ChildNodes[0].HasChildNodes || item.ParentNode.ParentNode.ParentNode.ChildNodes[1].HasChildNodes)
                                    {
                                        script.Insert(0, string.Format(" ) "
                                                                                                    + "{0}REFERENCES [dbo].[{1}] ( [{2}] ) ",
                                                                                                    Environment.NewLine,
                                                                                                    princName,
                                                                                                    item.Attributes["Name"].Value)); 
                                    }
                                    else
                                    {
                                        script.Insert(0, string.Format(" ) "
                                                                                                    + "{0}REFERENCES [dbo].[{1}] ( [{2}] ) "
                                                                                                    + "{0}     PRINT 'Foreign Key ''{3}'' added successfully !.'"
                                                                                                    + "{0}END"
                                                                                                    + "{0}ELSE"
                                                                                                    + "{0}BEGIN"
                                                                                                    + "{0}     PRINT 'The Foreign Key ''{3}'' already exists ! Please drop it & try again.'"
                                                                                                    + "{0}END;",
                                                                                                    Environment.NewLine,
                                                                                                    princName,
                                                                                                    item.Attributes["Name"].Value,
                                                                                                    item.ParentNode.ParentNode.ParentNode.Attributes["Name"].Value.StartsWith("FK_", StringComparison.CurrentCulture) ? item.ParentNode.ParentNode.ParentNode.Attributes["Name"].Value : "FK_" + item.ParentNode.ParentNode.ParentNode.Attributes["Name"].Value)); 
                                    }
                                                                                      

                                }
                                break;

                            case "Dependent":
                                string dependName = (item.ParentNode.Attributes["Role"].Value == item.ParentNode.ParentNode.ParentNode.ChildNodes[0].Attributes["Role"].Value) ? item.ParentNode.ParentNode.ParentNode.ChildNodes[0].Attributes["Type"].Value.Split('.')[1] : item.ParentNode.Attributes["Role"].Value;
                                if (!drop)
                                {
                                    script.Insert(0, string.Format(
                                                                                            "{0}IF NOT  EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{2}]') AND parent_object_id = OBJECT_ID(N'[dbo].[{1}]'))"
                                                                                           + "{0}BEGIN"
                                                                                           + "{0}      ALTER TABLE [dbo].[{1}]"// + item.ParentNode.Attributes["Role"].Value
                                                                                           + "{0}      WITH NOCHECK ADD CONSTRAINT [{2}]" //+ item.Attributes["Name"].Value
                                                                                           + "{0}      FOREIGN KEY ( [{3}] ",
                                                                                           Environment.NewLine,
                                                                                           dependName,
                                                                                           item.ParentNode.ParentNode.ParentNode.Attributes["Name"].Value.StartsWith("FK_", StringComparison.CurrentCulture) ? item.ParentNode.ParentNode.ParentNode.Attributes["Name"].Value : "FK_" + item.ParentNode.ParentNode.ParentNode.Attributes["Name"].Value,
                                                                                           item.Attributes["Name"].Value)); 
                                }
                                else
                                {
                                    script.Insert(0, string.Format(
                                                                                            "{0}IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{2}]') AND parent_object_id = OBJECT_ID(N'[dbo].[{1}]'))"
                                                                                           + "{0}BEGIN"
                                                                                           + "{0}      ALTER TABLE [dbo].[{1}]"// + item.ParentNode.Attributes["Role"].Value
                                                                                           + "{0}      DROP CONSTRAINT [{2}]" //+ item.Attributes["Name"].Value
                                                                                           + "{0}      PRINT 'Foreign Key ''{2}'' dropped successfully !.'"
                                                                                           + "{0}END"
                                                                                           + "{0}ELSE"
                                                                                           + "{0}BEGIN"
                                                                                           + "{0}      PRINT 'The Forgein Key ''{2}'' already dropped !'"
                                                                                           + "{0}END",
                                                                                           Environment.NewLine,
                                                                                           dependName,
                                                                                           item.ParentNode.ParentNode.ParentNode.Attributes["Name"].Value.StartsWith("FK_", StringComparison.CurrentCulture) ? item.ParentNode.ParentNode.ParentNode.Attributes["Name"].Value : "FK_" + item.ParentNode.ParentNode.ParentNode.Attributes["Name"].Value)); 
                                }
                                break;
                            case "End":
                                break;

                            default:
                                break;
                        }

                        if (!drop)
                        {
                            switch (item.Name)
                            {
                                case "OnDelete":
                                    if (item.NextSibling == null)
                                    {
                                        script.Append(string.Format(" ON DELETE {1} "
                                                                                                + "{0}     PRINT 'Foreign Key ''{2}'' added successfully !.'"
                                                                                                + "{0}END"
                                                                                                + "{0}ELSE"
                                                                                                + "{0}BEGIN"
                                                                                                + "{0}     PRINT 'The Foreign Key ''{2}'' already exists ! Please drop it & try again.'"
                                                                                                + "{0}END;",
                                                                                                Environment.NewLine,
                                                                                                item.Attributes["Action"].Value.ToUpper(),
                                                                                                item.ParentNode.ParentNode.Attributes["Name"].Value.StartsWith("FK_", StringComparison.CurrentCulture) ? item.ParentNode.ParentNode.Attributes["Name"].Value : "FK_" + item.ParentNode.ParentNode.Attributes["Name"].Value)); 
                                    }
                                    else
                                    {
                                        script.Append(string.Format(" ON DELETE {1} ", item.Attributes["Action"].Value.ToUpper()));
                                    }
                                    break;
                                case "OnUpdate":
                                    if (item.NextSibling == null)
                                    {
                                        script.Append(string.Format(" ON UPDATE {1} "
                                                                                                + "{0}     PRINT 'Foreign Key ''{2}'' added successfully !.'"
                                                                                                + "{0}END"
                                                                                                + "{0}ELSE"
                                                                                                + "{0}BEGIN"
                                                                                                + "{0}     PRINT 'The Foreign Key ''{2}'' already exists ! Please drop it & try again.'"
                                                                                                + "{0}END;",
                                                                                                Environment.NewLine,
                                                                                                item.Attributes["Action"].Value.ToUpper(),
                                                                                                item.ParentNode.ParentNode.Attributes["Name"].Value.StartsWith("FK_", StringComparison.CurrentCulture) ? item.ParentNode.ParentNode.Attributes["Name"].Value : "FK_" + item.ParentNode.ParentNode.Attributes["Name"].Value)); 
                                    }
                                    else
                                    {
                                        script.Append(string.Format(" ON UPDATE {1} ", item.Attributes["Action"].Value.ToUpper()));
                                    }
                                    break;
                                default:
                                    break;
                            } 
                        }
                    }
                }
            }

            return script.ToString();
        }

        private string GenerateEntityScript(XmlNodeList xNodeList)
        {
            StringBuilder script = new StringBuilder(string.Empty);
          
            foreach (XmlNode item in xNodeList)
            {
                if (item.HasChildNodes)
                {
                    string scrt = GenerateEntityScript(item.ChildNodes);

                    if (!item.Name.Equals("Key"))
                    {
                        strScript.Append(string.Format(
                                                        "IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{1}]') AND type in (N'U'))"
                                                       +"{0}BEGIN"
                                                       +"{0}CREATE TABLE [dbo].[{1}] ( {2} " 
                                                       +"{0});"
                                                       +"{0}     PRINT 'Table ''{1}'' created successfully !.'"
                                                       +"{0}END"
                                                       +"{0}ELSE"
                                                       +"{0}BEGIN"
                                                       +"{0}     PRINT 'The Table ''{1}'' already exists ! Please drop it & try again.'"
                                                       +"{0}END{0}",
                                                       Environment.NewLine,
                                                       item.Attributes["Name"].Value, 
                                                       scrt.ToString().Trim().TrimEnd(','))
                                                       ); 
                    }
                }
                else
                {
                    if (item.Name.Equals("Property"))
                    {
                        script.Append(string.Format("{0}",CreateColumn(item)));
                    }

                }
            }

            return script.ToString();
        }

        private string CreateColumn(XmlNode item)
        {
            StringBuilder column = new StringBuilder(string.Empty);

            foreach (XmlAttribute xAttri in item.Attributes)
            {
                switch (xAttri.Name)
                {
                    case "Name":
                        column.Append(string.Format("{0}[{1}] ", Environment.NewLine, xAttri.Value));
                        break;
                    case "Type":
                        column.Append(xAttri.Value.ToUpper() + " ");
                        if (item.Attributes["MaxLength"] != null)
                        {
                            column.Append(string.Format("( {0} ) ",item.Attributes["MaxLength"].Value));
                        }
                        break;
                    case "Nullable":
                        column.Append(string.Format("{0} ",xAttri.Value.Equals("false") ? "NOT NULL " : "NULL "));
                        break;
                    case "StoreGeneratedPattern":
                        column.Append(string.Format("{0} ( 1, 1 ) ", xAttri.Value.ToUpper()));
                        break;

                    default:
                        break;
                }
            }

            if (item.ParentNode.FirstChild.HasChildNodes)
            {
                if (item.ParentNode.FirstChild.ChildNodes[0].Attributes["Name"].Value.Equals(item.Attributes["Name"].Value))
                {
                    column.Append(string.Format(
                                             "{1}PRIMARY KEY CLUSTERED ( [{0}] ASC ) "
                                            +"{1}WITH ("
                                            +"{1}       ALLOW_PAGE_LOCKS = ON, "
                                            +"{1}       ALLOW_ROW_LOCKS = ON, " 
                                            +"{1}       PAD_INDEX = OFF, " 
                                            +"{1}       IGNORE_DUP_KEY = OFF, "
                                            +"{1}       STATISTICS_NORECOMPUTE = OFF "
                                            +"{1}      ) ", item.Attributes["Name"].Value,Environment.NewLine));
                }
            }

            return string.Format("{0}",column.Insert(column.Length, ", ").ToString());
        }

        private void btnOk_Click(object sender, RoutedEventArgs e)
        {
            txtProg.Text = "";

            IList fileNames = (IList)lstData.SelectedItems;

            if (fileNames.Count > 0)
            {
                popupListDB.IsOpen = false;

                rtxtTables.Document.Blocks.Clear();

                txtProg.Text = "Generating script.....";

                foreach (DBDetails fileName in fileNames)
                {
                    XmlDocument d = new XmlDocument();
                    d.Load(new FileStream(fileName.DatabaseFilename, FileMode.Open, FileAccess.Read, FileShare.Read));

                    string script = string.Empty;

                    XmlNodeList xmlNLEntityType = d.DocumentElement.GetElementsByTagName("EntityType");

                    if (xmlNLEntityType.Count > 0)
                    {
                        colScript.Clear();

                        strScript = new StringBuilder(string.Empty);

                        if (fileName.IsDropChecked)
                        {
                            colScript.Add(string.Format("{0}--Creating database '{1}' starts here...--"
                                                        + "{0} USE [master];{0}GO"
                                                        + "{0}IF EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE  [name] = N'[{1}]') AND DB_ID('[{1}]') IS NOT NULL"
                                                        + "{0}BEGIN"
                                                        + "{0}     CREATE DATABASE [{1}]; "
                                                        + "{0}     PRINT 'Database ''{1}'' created successfully !.'"
                                                        + "{0}END"
                                                        + "{0}ELSE"
                                                        + "{0}BEGIN"
                                                        + "{0}     PRINT 'The Database ''{1}'' already exists ! Dropping it...'"
                                                        + "{0}     DROP DATABASE [{1}]; "
                                                        + "{0}     CREATE DATABASE [{1}]; "
                                                        + "{0}     PRINT 'Database ''{1}'' created successfully !.'"
                                                        + "{0}END",
                                                        Environment.NewLine,
                                                        fileName.DatabaseName)); 
                        }
                        else
                        {
                            colScript.Add(string.Format("{0}--Creating database '{1}' starts here...--"
                                                        + "{0} USE [master];{0}GO"
                                                        + "{0}IF NOT EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE  [name] = N'[{1}]') AND DB_ID('[{1}]') IS NOT NULL"
                                                        + "{0}BEGIN"
                                                        + "{0}     CREATE DATABASE [{1}]; "
                                                        + "{0}     PRINT 'Database ''{1}'' created successfully !.'"
                                                        + "{0}END"
                                                        + "{0}ELSE"
                                                        + "{0}BEGIN"
                                                        + "{0}     PRINT 'The Database ''{1}'' already exists !'"
                                                        + "{0}END",
                                                        Environment.NewLine,
                                                        fileName.DatabaseName)); 
                        }

                        colScript.Add(string.Format(
                                                        "{0} GO"
                                                       + "{0} USE [master];{0}GO"
                                                       + "{0}IF EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE  [name] = N'[{1}]') AND DB_ID('[{1}]') IS NOT NULL"
                                                       + "{0}    BEGIN"
                                                       + "{0}         ALTER DATABASE [{1}]"
                                                       + "{0}               SET ANSI_NULLS OFF,"
                                                       + "{0}               ANSI_PADDING ON,"
                                                       + "{0}               ANSI_WARNINGS OFF,"
                                                       + "{0}               ARITHABORT OFF,"
                                                       + "{0}               CONCAT_NULL_YIELDS_NULL OFF,"
                                                       + "{0}               NUMERIC_ROUNDABORT OFF,"
                                                       + "{0}               QUOTED_IDENTIFIER ON,"
                                                       + "{0}               ANSI_NULL_DEFAULT OFF,"
                                                       + "{0}               CURSOR_DEFAULT LOCAL,"
                                                       + "{0}               RECOVERY SIMPLE,"
                                                       + "{0}               CURSOR_CLOSE_ON_COMMIT OFF,"
                                                       + "{0}               AUTO_CREATE_STATISTICS ON,"
                                                       + "{0}               AUTO_SHRINK OFF,"
                                                       + "{0}               AUTO_UPDATE_STATISTICS ON,"
                                                       + "{0}               RECURSIVE_TRIGGERS OFF "
                                                       + "{0}               WITH ROLLBACK IMMEDIATE;"
                                                       + "{0}         ALTER DATABASE [{1}] "
                                                       + "{0}               SET AUTO_CLOSE OFF "
                                                       + "{0}               WITH ROLLBACK IMMEDIATE;"
                                                       + "{0}    END"
                                                       + "{0}    ELSE"
                                                       + "{0}    BEGIN"
                                                       + "{0}         PRINT 'The Database ''{1}'' not exists ! Cant set DB properties.'"
                                                       + "{0}    END",
                                                       Environment.NewLine,
                                                       fileName.DatabaseName));

                        
                        colScript.Add(string.Format("--Creating database '{1}' ends here... "
                                                   +"{0}USE [{1}] "
                                                   +"{0}GO",
                                                   Environment.NewLine,
                                                   fileName.DatabaseName));

                        if (fileName.IsDropChecked)
                        {
                            colScript.Add("--Dropping association for the tables starts here--");

                            strScript = new StringBuilder(string.Empty);

                            XmlNodeList xmlNLDropAssociation = d.DocumentElement.GetElementsByTagName("Association");

                            if (xmlNLDropAssociation.Count > 0)
                            {
                                GenerateConstraintScript(xmlNLDropAssociation,fileName.IsDropChecked);
                            }

                            colScript.Add("--Dropping association for the tables ends here--");
                        }

                        colScript.Add(string.Format("{0}--Creating tables for DataBase '{1}' starts here --",Environment.NewLine,fileName.DatabaseName));

                        GenerateEntityScript(xmlNLEntityType);

                        colScript.Add(strScript.ToString());

                        colScript.Add(string.Format("--Creating tables for DataBase '{0}' ends here --",fileName.DatabaseName));

                        XmlNodeList xmlNLAddAssociation = d.DocumentElement.GetElementsByTagName("Association");

                        if (xmlNLAddAssociation.Count > 0)
                        {
                            colScript.Add("--Creating association for the tables starts here--");

                            strScript = new StringBuilder(string.Empty);

                            GenerateConstraintScript(xmlNLAddAssociation,false);

                            colScript.Add("--Creating association for the tables ends here--");

                            strScript = new StringBuilder(string.Empty);

                            colScript.Add(GenerateConstraintCheckScript(xmlNLAddAssociation));

                            foreach (var item in colScript)
                            {
                                rtxtTables.AppendText(item.ToString() + Environment.NewLine);
                            }
                        }
                    }
                }

                txtProg.Text = "Done.....";
            }
            else
            {
                txtError.Text = "Please select a database ! ";
            }

        }

        private void btnCancel_Click(object sender, RoutedEventArgs e)
        {
            popupListDB.IsOpen = false;
        }

        class DBDetails
        {
            public string DatabaseName { get; set; }
            public string DatabaseSize { get; set; }
            public bool IsDropChecked { get; set; }
            public string DatabaseFilename { get; set; }
            
            public DBDetails()
            {
                DatabaseFilename = string.Empty;
                DatabaseName = string.Empty;
                IsDropChecked = true;
                DatabaseSize = string.Empty;
            }
            
        }

        private void chkDrop_Click(object sender, RoutedEventArgs e)
        {
            ((e.OriginalSource as System.Windows.Controls.CheckBox).DataContext as DBDetails).IsDropChecked = (e.OriginalSource as System.Windows.Controls.CheckBox).IsChecked.Value;
        }

        private void lstData_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            txtError.Text = "";
        }

    }
}
